/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.FunctionDTO;
import east2west.interfaces.FunctionInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author nguyendat
 */
public class FunctionDAO implements FunctionInterface {

    Connection con = null;
    CallableStatement call = null;
    FunctionDTO dto = null;

    public ArrayList<FunctionDTO> readByAll() {
        ArrayList<FunctionDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Function");
            ResultSet rs = call.executeQuery();
            list = new ArrayList<FunctionDTO>();
            while (rs.next()) {
                dto = new FunctionDTO();
                dto.setFunctionId(rs.getInt(1));
                dto.setFunctionName(rs.getString(2));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public FunctionDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_RoleFunction where RoleId = ?");
            call.setInt(1, id);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                dto = new FunctionDTO();
                dto.setFunctionId(rs.getInt(1));
                dto.setFunctionName(rs.getString(2));
            }
            rs.close();
            call.close();
            return dto;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<FunctionDTO> findByName(String name) {
        ArrayList<FunctionDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Function_FindName(?)}");
            call.setString(1, name);

            ResultSet rs = call.executeQuery();
            list = new ArrayList<FunctionDTO>();
            while (rs.next()) {
                dto = new FunctionDTO();
                dto.setFunctionId(rs.getInt(1));
                dto.setFunctionName(rs.getString(2));

                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<FunctionDTO> findByDTO(FunctionDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean create(FunctionDTO function) {
        try {
            function = new FunctionDTO();
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Insert_Function(?)}");
            call.setString(1, function.getFunctionName());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex1);
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean update(FunctionDTO function) {
        try {
            function = new FunctionDTO();
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Update_Function(?,?)}");
            call.setInt(1, function.getFunctionId());
            call.setString(2, function.getFunctionName());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex1);
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean delete(FunctionDTO function) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Delete_Function(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex1);
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
                Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ArrayList<FunctionDTO> findByRoleId(int roleId) {
        try {
            ArrayList<FunctionDTO> list = new ArrayList<FunctionDTO>();
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_RoleFunction where RoleId = ?");
            call.setInt(1, roleId);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                dto = new FunctionDTO();
                dto.setFunctionId(rs.getInt(1));
                dto.setFunctionName(rs.getString(2));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }
}
